/**
 * 公共文件
 */

const mysql = require('mysql2/promise');
const { host, dbPort, user, password, database } = require('../config.json');
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(database, user, password, {
    host,
    port: dbPort,
    // logging: (...msgs) => console.log(msgs),
    dialect: 'mysql'
});

const id = {
    id: {
        type: DataTypes.INTEGER.UNSIGNED,
        autoIncrement: true,
        primaryKey: true,
    }
};

const define = (name, cols, options) => sequelize.define(name, {
    ...id,
    ...cols
}, {
    timestamps: false,
    tableName: name,
    ...options
});

const dictionary = define('dictionary', {
    name: {
        type: DataTypes.STRING(20),
        allowNull: false,
    },
    type: {
        type: DataTypes.TINYINT.UNSIGNED,
        allowNull: false,
    }
});

const teachers = define('teachers', {
    name: {
        type: DataTypes.STRING(20),
        allowNull: false,
    },
    sex: {
        type: DataTypes.ENUM('男', '女'),
        allowNull: false,
    },
    work_no: {
        type: DataTypes.MEDIUMINT,
        allowNull: false,
    },
    salary: {
        type: DataTypes.MEDIUMINT.UNSIGNED,
        allowNull: false,
    },
    position: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        // references: {
        //     model: dictionary,
        //     key: 'id'
        // }
    },
    idNo: {
        type: DataTypes.CHAR(18),
        allowNull: false,
    },
    phone: {
        type: DataTypes.CHAR(11),
        allowNull: false,
    },
    address: {
        type: DataTypes.STRING(100),
        allowNull: false,
    },
    subject: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        references: {
            model: dictionary,
            key: 'id'
        }
    },
    status: {
        type: DataTypes.BOOLEAN,
    },
    username: {
        type: DataTypes.STRING(20),
        allowNull: false,
    },
    password: {
        type: DataTypes.CHAR(32),
        allowNull: false,
    }
});

teachers.hasOne(dictionary, { foreignKey: 'id', sourceKey: 'position', as: 'p' });
teachers.hasOne(dictionary, { foreignKey: 'id', sourceKey: 'subject', as: 's' });
// dictionary.belongsTo(teachers, { foreignKey: 'id', sourceKey: 'position', as: 'p' });
// dictionary.belongsTo(teachers, { foreignKey: 'id', sourceKey: 'subject', as: 's' });

/**
 * 类型转换
 * @param {*} field 
 * @param {*} next 
 * @returns 
 */
const typeCast = (field, next) => {
    if (field.type === 'BIT' && field.length === 1) {
        return (field.buffer().toString('hex') === '01'); // 1 = true, 0 = false
    } else {
        return next();
    }
}

/**
 * 连接池
 */
const pool = mysql.createPool({
    host,
    port: dbPort,
    user,
    password,
    database,
    typeCast
});

/**
 * 查询语句并且只获取数据
 * @param {*} sql 要执行的sql语句
 * @param {*} params 要传递的参数
 * @returns 
 */
const query = async (sql, params) => {
    const [result] = await pool.query(sql, params);
    return result
}

/**
 * 查询第一条数据
 * @param {*} sql 要执行的sql语句
 * @param {*} params 要传递的参数
 * @returns 
 */
const queryFirst = async (sql, params) => {
    const [result] = await query(sql, params);
    return result;
}

/**
 * 执行并且开启事物
 * @param  {...any} sql 要执行的多条sql语句
 */
const queryTrans = async (...sql) => {
    const conn = await pool.getConnection();
    try {
        await conn.beginTransaction();
        const data = await Promise.all(sql.map(c => conn.query(c)));
        await conn.commit();
        return data;
    } catch (error) {
        await conn.rollback();
        throw error;
    }
    finally {
        conn.release();
    }
}

/**
 * 查询分页
 * @param {*} obj 查询对象
 * @param {*} queryList 查询列表的SQL语句
 * @param {*} queryCount 查询综艺套数的SQL语句
 * @returns 
 */
const queryPage = async (obj, queryList, queryCount) => {
    const conn = await pool.getConnection();
    const { pageNo, pageSize } = obj;
    try {
        const [[list], [[count]]] = await Promise.all([
            conn.query(queryList, [(pageNo - 1) * pageSize, pageNo * pageSize]),
            conn.query(queryCount)
        ]);
        return [list, count.count];
    } catch (error) {
        throw error;
    }
    finally {
        conn.release();
    }
}

module.exports = {
    pool,
    query,
    sequelize,
    queryPage,
    queryFirst,
    queryTrans,
    format: mysql.format,
}